ViewModels are useful for the efficient fetching of data since they declaratively explain what data will be used. This enables MDriven to scan through the expressions and fetch data with fewer queries.

(NOTE: Most of the things described below are now automatically handled by the QueryPlanner and you don't need to create fetch hints.)

If you have a root of something that has a list of details and the details, in turn, fetch even more details, you could easily end up with x*y*z queries to the database.

Since the ViewModel uses expressions that may be stackable, we can easily fetch all x, all x.y, and all x.y.z with 3 queries. This is standard behavior when using the MDriven ViewModels.

• When you use Seekers, the result is collected in a collection variable commonly named vSeekerResult.

The search result is not covered by the standard efficient fetch algorithm because this data is not available at load time. This may, in certain situations, be less than ideal; so, we have added strategies to improve the efficient fetching in ViewModels using Seekers – this article explains how.

All the ideas implemented and explained below came from a real case where I had a fairly complex UI to visualize and manage demand forecasts for articles:

There are thousands of objects shown in this one single image. It is a Seeker, but it can also get data from a list tied to the current user that is called Favorites.

Even with the normal Efficient fetch turned on, this UI typically spewed out up to 900 questions to the Persistence Server.

To fully understand where the questions came from, I used the Debugger:

Check the PMapper and you will see all the PMapper calls.

The ones that we look for are the ones that fetch only 1 object – these are candidates for improvement.

When the log sees that only 1 object is fetched, it also shows the ClassId.

You can look up ClassId to name in the Debugger:

Once we know what created the queries, we can think of ways to fix it.

The new strategy to improve the efficient fetch further is to add a ViewModel Nesting with fetching hints. Any ViewModel nesting with a name that starts with “FetchHints” will be found, and the columns within will be executed on any list that seeker logic delivers.

Like this:

Working like this, I could reduce the load from this UI from 900 queries to 30-something.

The problem with the search was solved, but when the data came from the other source – the user favorites – this logic is not applied.

I had to devise a way to allow me to tell the ViewModel that this logic should execute on a list of objects.

To solve this, a new standard variable was introduced in ViewModels: selfVM

• The selfVM variable is a reference to the ViewModel we work on – self is the object context as before – but selfVM is the ViewModel holding the objects.

I think that this is a good extension that may solve some limitations we have had before.

• The selfVM introduces 3 new operations: ExecuteFetchHints, Save, and ExecuteAction.
• The ExecuteFetchHints is the one I was after in this case. Save and ExecuteAction was added since there has been a demand for programmatic access to these functions.

My code for showing the users' favorites in the UI above was extended like this:

vSeekerResult->Clear;
Singleton.oclSingleton.User.FavoriteArticle->select(fa|fa.Filter.SqlLike(vFavFilter+’%’) or vFavFilter.IsNullOrEmpty).FavoriteArticles->collect(a|
— Add the SA for the users country
let sa=a.SalesArticles->select(sa|sa.MarketingCompany=self)->first in
  (
     if sa.isnull then
        vSeekerResult
     else
        vSeekerResult.Add(sa)
     endif    
  )
);
selfVM.ExecuteFetchHints(vSeekerResult) 

Then, the same efficient fetching could be applied to the data coming this way as well.

Also see: How to use the ExecutePS function in selfVM

The MDriven Book - See: Introducing MDriven Turnkey